/**
 * 
 */
package com.iuie.ns.frame.utils;

import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import jxl.CellView;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import com.iuie.ns.frame.free.excel.beans.SheetColumn;

/**
 * @description 输出Excel的工具类
 * @author lj
 * @createTime 2016年10月28日 下午2:43:45
 */
public final class OutputExcelUtil {

	private OutputExcelUtil() {

	}
	
	/**
	 * 定义SheetColumn中定义的水平方向排列方式与JXL中定义的排列方式的对应关系
	 */
	private static final  Map<Integer, Object> JXL_ALIGN_MAPS = new HashMap<Integer, Object>(){
		private static final long serialVersionUID = -5062244366708953094L;
		{
			put(SheetColumn.ALIGN_CENTER, Alignment.CENTRE);
			put(SheetColumn.ALIGN_LEFT, Alignment.LEFT);
			put(SheetColumn.ALIGN_RIGHT, Alignment.RIGHT);
		}
	};
	/**
	 * 
	 * @description 通过JXL的API导出Excel的方法
	 * @author lj
	 * @date 2016年10月30日 下午12:34:08
	 * @param os 输出流，一般为文件流，将导出的Excel输出到文件中
	 * @param title 工作表的名称
	 * @param headers java对象与Sheet工作表中数据的对应关系
	 * @param dataset 数据
	 * @param pattern Date类型转换的表达式：yyyy-MM-dd/yyyy-MM-dd HH:mm:ss
	 * @throws IOException
	 * @throws RowsExceededException
	 * @throws WriteException
	 */
	@Deprecated
	public static <T> void exportByJxl(OutputStream os, String title, List<SheetColumn> headers, Collection<T> dataset,
			String pattern) throws IOException, RowsExceededException, WriteException {
		// 声明一个工作薄
		WritableWorkbook workbook = Workbook.createWorkbook(os);
		// 生成一个表格
		WritableSheet sheet = workbook.createSheet(title, 0);

		WritableFont wf2 = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false,
				UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); // 定义格式,字体,下划线, 斜体 粗体, 颜色
		WritableCellFormat wcfTitle = new WritableCellFormat(wf2);
		wcfTitle.setBackground(jxl.format.Colour.IVORY); // 象牙白
		wcfTitle.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.BLACK); // BorderLineStyle边框
		// wcfTitle.setVerticalAlignment(VerticalAlignment.CENTRE); //设置垂直对齐
		wcfTitle.setAlignment(Alignment.CENTRE); // 设置水平对齐

		for (short i = 0; i < headers.size(); i++) {
			CellView navCellView = new CellView();
			navCellView.setAutosize(true);
			navCellView.setSize(headers.get(i).getColumnWidth() * 256);
			sheet.setColumnView(i, navCellView);
			sheet.addCell(new Label(i, 0, headers.get(i).getColumnName(), wcfTitle));
		}
		
		List<WritableCellFormat> columnStyle = new ArrayList<WritableCellFormat>();
		for (short i = 0; i < headers.size(); i++) {
			WritableCellFormat contentCellFormat = new WritableCellFormat();
			contentCellFormat.setAlignment((Alignment) headers.get(i).getAlign(JXL_ALIGN_MAPS));
			columnStyle.add(contentCellFormat);
		}
		
		// 遍历集合数据，产生数据行
		Iterator<T> it = dataset.iterator();
		int index = 0;
		try {
			while (it.hasNext()) {
				index++;
				T t = (T) it.next();
				Label label = null;
				// 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
				for (short i = 0; i < headers.size(); i++) {
					String getMethodName = headers.get(i).getMethodNameByColumnKey();
					Class<? extends Object> tCls = t.getClass();
					Method getMethod = tCls.getMethod(getMethodName, new Class[] {});
					Object value = getMethod.invoke(t, new Object[] {});
					// 判断值的类型后进行强制类型转换
					String textValue = null;
					if (value instanceof String) {
						textValue = String.valueOf(value);
					} else if (value instanceof Date) {
						Date date = (Date) value;
						SimpleDateFormat sdf = new SimpleDateFormat(pattern);
						textValue = sdf.format(date);
					}
					label = new Label(i, index, textValue,columnStyle.get(i));
					sheet.addCell(label);
				}
			}
		} catch (SecurityException e) {
			e.printStackTrace();
		} catch (NoSuchMethodException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		} finally {
			// 清理资源
			workbook.write();
			workbook.close();
			os.flush();
			os.close();
		}
	}

	/**
	 * 定义SheetColumn中定义的水平方向排列方式与POI中定义的排列方式的对应关系
	 */
	public static final  Map<Integer, Object> POI_ALIGN_MAPS = new HashMap<Integer, Object>(){
		private static final long serialVersionUID = 8289899429475411596L;
		{
			put(SheetColumn.ALIGN_CENTER, HSSFCellStyle.ALIGN_CENTER);
			put(SheetColumn.ALIGN_LEFT, HSSFCellStyle.ALIGN_LEFT);
			put(SheetColumn.ALIGN_RIGHT, HSSFCellStyle.ALIGN_RIGHT);
		}
	};
	
	/**
	 * poi回调
	 * @author iuie
	 *
	 */
	public interface Callback {
		<T> T handleRowData(T data);
	}
	
	/**
	 * 
	 * @description 通过POI的API导出Excel的方法
	 * @author lj
	 * @date 2016年10月30日 下午12:34:08
	 * @param os 输出流，一般为文件流，将导出的Excel输出到文件中
	 * @param extension 文件名后缀
	 * @param title 工作表的名称
	 * @param headers java对象与Sheet工作表中数据的对应关系
	 * @param dataset 数据
	 * @param pattern Date类型转换的表达式：yyyy-MM-dd/yyyy-MM-dd HH:mm:ss
	 * @param callback 回调
	 * @throws IOException
	 * @throws RowsExceededException
	 * @throws WriteException
	 */
	public static <T> void exportByPoi(OutputStream os, String extension, String title, List<SheetColumn> headers,List<T> lst,
			String pattern, Callback callback) throws IOException, RowsExceededException, WriteException {
		if (lst == null) {
			lst = new ArrayList<>();
		}
		
		if (StringUtils.equalsIgnoreCase(extension, "xlsx")) {
			exportByExtensionIsXlsx(os, title, headers, lst, pattern, callback);
		} else if (StringUtils.equalsIgnoreCase(extension, "xls")) {
			exportByExtensionIsXls(os, title, headers, lst, pattern, callback);
		}
	}
	
	/**
	 * @description 通过POI的API导出Excel的方法
	 * @author lj
	 * @date 2016年10月30日 下午12:34:08
	 * @param os 输出流，一般为文件流，将导出的Excel输出到文件中
	 * @param title 工作表的名称
	 * @param headers java对象与Sheet工作表中数据的对应关系
	 * @param dataset 数据
	 * @param pattern Date类型转换的表达式：yyyy-MM-dd/yyyy-MM-dd HH:mm:ss
	 * @throws IOException
	 * @throws RowsExceededException
	 * @throws WriteException
	 */
	public static <T> void exportByPoi(OutputStream os, String extension, String title, List<SheetColumn> headers, List<T> lst,
			String pattern) throws IOException, RowsExceededException, WriteException {
		OutputExcelUtil.exportByPoi(os, extension, title, headers, lst, pattern, null);
	}
	
	/**
	 * 通过POI的API导出Excel的方法
	 * @author lj
	 * @date 2018-01-16 15:06:47
	 * @param os 输出流，一般为文件流，将导出的Excel输出到文件中
	 * @param extension 文件后缀名
	 * @param title 工作表的名称
	 * @param clazz 用于获取headers的VO类，该VO类中使用了注解SheetColumnDefine
	 * @param lst 数据
	 * @param pattern Date类型转换的表达式：yyyy-MM-dd/yyyy-MM-dd HH:mm:ss
	 * @throws IOException
	 * @throws RowsExceededException
	 * @throws WriteException
	 */
	public static <T> void exportByPoi(OutputStream os, String extension, String title, Class<?> clazz, List<T> lst,
			String pattern) throws IOException, RowsExceededException, WriteException {
		List<SheetColumn> headers = SheetColumnUtils.getSheetColumns(clazz);
		OutputExcelUtil.exportByPoi(os, extension, title, headers, lst, pattern, null);
	}
	
	/**
	 * 通过POI的API导出Excel的方法
	 * @author lj
	 * @date 2018-01-16 15:06:47
	 * @param os 输出流，一般为文件流，将导出的Excel输出到文件中
	 * @param extension 文件后缀名
	 * @param title 工作表的名称
	 * @param clazz 用于获取headers的VO类，该VO类中使用了注解SheetColumnDefine
	 * @param lst 数据
	 * @param pattern Date类型转换的表达式：yyyy-MM-dd/yyyy-MM-dd HH:mm:ss
	 * @param callback 回调
	 * @throws IOException
	 * @throws RowsExceededException
	 * @throws WriteException
	 */
	public static <T> void exportByPoi(OutputStream os, String extension, String title, Class<?> clazz, List<T> lst,
			String pattern, Callback callback) throws IOException, RowsExceededException, WriteException {
		List<SheetColumn> headers = SheetColumnUtils.getSheetColumns(clazz);
		OutputExcelUtil.exportByPoi(os, extension, title, headers, lst, pattern, callback);
	}
	
	private static <T> void exportByExtensionIsXls(OutputStream os, String title, List<SheetColumn> headers,List<T> lst,
			String pattern, Callback callback) throws IOException, RowsExceededException, WriteException {
		// 声明一个工作薄
		HSSFWorkbook workbook = new HSSFWorkbook();
		
		// 生成一个表格
		HSSFSheet sheet = workbook.createSheet(title);
		// 设置表格默认列宽度为15个字节
		sheet.setDefaultColumnWidth(15);
		// 生成一个样式
		HSSFCellStyle style = workbook.createCellStyle();
		// 设置这些样式
		style.setFillForegroundColor(HSSFColor.TEAL.index); // 前景色
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 设置垂直居中
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 生成一个字体
		HSSFFont font = workbook.createFont();
		font.setColor(HSSFColor.WHITE.index);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		// 把字体应用到当前的样式
		style.setFont(font);
		
		// 生成另一个字体
		HSSFFont font2 = workbook.createFont();
		font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);

		// 产生表格标题行
		HSSFRow row = sheet.createRow(0); // 标题行
		row.setHeight((short) (1.5*250)); // 设置1.5倍行距
		
		for (int i = 0; i < headers.size(); i++) {
			HSSFCell cell = row.createCell(i);
			cell.setCellStyle(style);
			// 宽度大于0时才设置宽度
			if (headers.get(i).getColumnWidth()>0) {
				sheet.setColumnWidth(i, headers.get(i).getColumnWidth()*256);
			} else {
				sheet.setColumnWidth(i, 20*256);
			}
			HSSFRichTextString text = new HSSFRichTextString(headers.get(i).getColumnName());
			cell.setCellValue(text);
		}
		
		List<HSSFCellStyle> columnStyle = new ArrayList<HSSFCellStyle>();
		for (int i = 0; i < headers.size(); i++) {
			HSSFCellStyle style2 = workbook.createCellStyle();
			style2.setFillBackgroundColor(HSSFColor.LIGHT_TURQUOISE.index);
			style2.setFillPattern(HSSFCellStyle.NO_FILL);
			style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			style2.setAlignment((Short) headers.get(i).getAlign(POI_ALIGN_MAPS));
			// 把字体应用到当前的样式
			style2.setFont(font2);
			columnStyle.add(style2);
		}
		// 遍历集合数据，产生数据行
		Iterator<T> it = lst.iterator();
		int index = 0;
		try {
			while (it.hasNext()) {
				index++;
				row = sheet.createRow(index);
				row.setHeight((short) (1.5*250)); // 1.5倍行距
				T t = (T) it.next();
				if (callback != null) {
					t = callback.handleRowData(t);
				}
				// 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
				for (int i = 0; i < headers.size(); i++) {
					HSSFCell cell = row.createCell(i);
					// 生成并设置另一个样式
					cell.setCellStyle(columnStyle.get(i));
					String getMethodName = headers.get(i).getMethodNameByColumnKey();
					Class<? extends Object> tCls = t.getClass();
					Method getMethod = tCls.getMethod(getMethodName, new Class[] {});
					Object value = getMethod.invoke(t, new Object[] {});
					// 判断值的类型后进行强制类型转换
					String textValue = null;
					if (value != null) {
						if (value instanceof String || value instanceof Integer || value instanceof Double || value instanceof Float) {
							textValue = String.valueOf(value);
						} else if (value instanceof Date) {
							Date date = (Date) value;
							SimpleDateFormat sdf = new SimpleDateFormat(pattern);
							textValue = sdf.format(date);
						} else {
							textValue = value.toString();
						}
					}
					cell.setCellValue(textValue);
				}
			}
		} catch (SecurityException e) {
			e.printStackTrace();
		} catch (NoSuchMethodException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		} finally {
			// 清理资源
			workbook.write(os);
			os.flush();
			os.close();
		}
	}
	
	private static <T> void exportByExtensionIsXlsx(OutputStream os, String title, List<SheetColumn> headers,List<T> lst,
			String pattern, Callback callback) throws IOException, RowsExceededException, WriteException {
		// 声明一个工作薄
		org.apache.poi.ss.usermodel.Workbook workbook = new SXSSFWorkbook();
		// 生成一个表格
		Sheet sheet = workbook.createSheet(title);
		// 设置表格默认列宽度为15个字节
		sheet.setDefaultColumnWidth(15);
		// 生成一个样式
		CellStyle style = workbook.createCellStyle();
		// 设置这些样式
		style.setFillForegroundColor(HSSFColor.TEAL.index); // 前景色
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 设置垂直居中
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 生成一个字体
		Font font = workbook.createFont();
		font.setColor(HSSFColor.WHITE.index);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		// 把字体应用到当前的样式
		style.setFont(font);
		
		// 生成另一个字体
		Font font2 = workbook.createFont();
		font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);

		// 产生表格标题行
		Row row = sheet.createRow(0); // 标题行
		row.setHeight((short) (1.5*250)); // 设置1.5倍行距
		
		for (int i = 0; i < headers.size(); i++) {
			Cell cell = row.createCell(i);
			cell.setCellStyle(style);
			// 宽度大于0时才设置宽度
			if (headers.get(i).getColumnWidth()>0) {
				sheet.setColumnWidth(i, headers.get(i).getColumnWidth()*256);
			} else {
				sheet.setColumnWidth(i, 20*256);
			}
			HSSFRichTextString text = new HSSFRichTextString(headers.get(i).getColumnName());
			cell.setCellValue(text);
		}
		
		List<CellStyle> columnStyle = new ArrayList<CellStyle>();
		for (int i = 0; i < headers.size(); i++) {
			CellStyle style2 = workbook.createCellStyle();
			style2.setFillBackgroundColor(HSSFColor.LIGHT_TURQUOISE.index);
			style2.setFillPattern(HSSFCellStyle.NO_FILL);
			style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			style2.setAlignment((Short) headers.get(i).getAlign(POI_ALIGN_MAPS));
			// 把字体应用到当前的样式
			style2.setFont(font2);
			columnStyle.add(style2);
		}
		// 遍历集合数据，产生数据行
		Iterator<T> it = lst.iterator();
		int index = 0;
		try {
			while (it.hasNext()) {
				index++;
				row = sheet.createRow(index);
				row.setHeight((short) (1.5*250)); // 1.5倍行距
				T t = (T) it.next();
				if (callback != null) {
					t = callback.handleRowData(t);
				}
				// 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
				for (int i = 0; i < headers.size(); i++) {
					Cell cell = row.createCell(i);
					// 生成并设置另一个样式
					cell.setCellStyle(columnStyle.get(i));
					String getMethodName = headers.get(i).getMethodNameByColumnKey();
					Class<? extends Object> tCls = t.getClass();
					Method getMethod = tCls.getMethod(getMethodName, new Class[] {});
					Object value = getMethod.invoke(t, new Object[] {});
					// 判断值的类型后进行强制类型转换
					String textValue = null;
					if (value != null) {
						if (value instanceof String || value instanceof Integer || value instanceof Double || value instanceof Float) {
							textValue = String.valueOf(value);
						} else if (value instanceof Date) {
							Date date = (Date) value;
							SimpleDateFormat sdf = new SimpleDateFormat(pattern);
							textValue = sdf.format(date);
						} else {
							textValue = value.toString();
						}
					}
					cell.setCellValue(textValue);
				}
			}
		} catch (SecurityException e) {
			e.printStackTrace();
		} catch (NoSuchMethodException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		} finally {
			// 清理资源
			workbook.write(os);
			os.flush();
			os.close();
		}
	}
	
	/*
public class UserVO implements Serializable {

	private static final long serialVersionUID = 1628426285032231455L;

	@SheetColumnDefine(name="用户名", weight=1)
	private String username;
	
	@SheetColumnDefine(name="年龄", weight=2)
	private Integer age;
	
	@SheetColumnDefine(name="手机号", weight=3)
	private String telNo;
	
	@SheetColumnDefine(name="用户ID", weight=0)
	private String id;

	public String getUsername() {
		return username;
	}

	public UserVO setUsername(String username) {
		this.username = username;
		return this;
	}

	public Integer getAge() {
		return age;
	}

	public UserVO setAge(Integer age) {
		this.age = age;
		return this;
	}

	public String getTelNo() {
		return telNo;
	}

	public UserVO setTelNo(String telNo) {
		this.telNo = telNo;
		return this;
	}

	public String getId() {
		return id;
	}

	public UserVO setId(String id) {
		this.id = id;
		return this;
	}
	
	
}
	 public static void main(String[] args) throws IOException, RowsExceededException, WriteException {
		List<UserVO> list = new ArrayList<>();
		long start = System.currentTimeMillis();
		System.out.println("准备数据--开始 --> " + start);
		for (int i = 0; i < 100*10000; i ++ ) {
			UserVO user = new UserVO();
			user.setAge(i % 100 + 1)
					.setId(StringUtils.uuid())
					.setTelNo(StringUtils.randonTelNo())
					.setUsername("Tom" + StringUtils.randonLong(0, 100));
			list.add(user);
		}
		System.out.println("准备数据--结束 ，耗时--> " + ( System.currentTimeMillis() - start));
		System.out.println("开始导出 ，耗时--> " + ( System.currentTimeMillis() - start));
		File file = new File("D:\\abc.xlsx");
		if (file.exists()) {
			file.delete();
		}
		file.createNewFile();
		OutputStream os = new FileOutputStream(file);
		
		OutputExcelUtil.exportByPoi(os, "xlsx", "abc", UserVO.class, list, "yyyy-MM-dd HH:mm:ss");
		System.out.println("导出结束 ，耗时--> " + ( System.currentTimeMillis() - start));
	}*/
}
